﻿using DBUtility;
using Jmit.Common;
using Jmit.Deyi.AutoTask;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection.Emit;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml;

namespace Jmit.Deyi.Job
{
    // 不允许此 Job 并发执行任务（禁止新开线程执行）
    // [DisallowConcurrentExecution]

    // DPS数据同步-DPS下载WMS数据
    public class DPSJob_down : IJob
    {

        /// <summary>
        /// 定时任务：从orcale取数据同步到MySQL
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public Task Execute(IJobExecutionContext context)
        {
            return Task.Factory.StartNew(() =>
            {


                WMSTODPS_dps_pick();
                   

            });
        }


        /// <summary>
        /// dps_pick 表数据 （wms to dps）
        /// </summary>
        private void WMSTODPS_dps_pick()
        {


            string sync_message = "";
            string sync_result = "";
            int sync_count = 0;
            

            string strCnn_from = AppConfigManager.LoadConfig().dbConn_wms;
            string strCnn_to = AppConfigManager.LoadConfig().dbConn_dps;

            try
            {

                LogHelpter.AddLog("WMS ==》DPS [dps_pick] 数据下发");

                List<string> sqlList = new List<string>();
                List<string> sqlList_update = new List<string>();

                // 更新 临时状态 8
                dbmanagerSql.ExecuteScalarSql(strCnn_from, "update  wms.dps_pick set downflag=8 where  downflag = 0 ");

                // 取出数据
                string sql_from = "";
                sql_from = $" select * from wms.dps_pick where  downflag = 8 ";   // 同步每日数据


                int dataCount = 0;
                int dataCount_dtl = 0;
                DataTable DT_pick = OpDB.GetDataTable(strCnn_from, sql_from);
                if (DT_pick != null && DT_pick.Rows.Count > 0)
                {
                    dataCount = DT_pick.Rows.Count;
                    LogHelpter.AddLog("行数：" + dataCount);



                    foreach (DataRow row in DT_pick.Rows)
                    {

                        sqlList = new List<string>();
                        sqlList_update = new List<string>();

                        string billno = String2Json(row["billno"].ToString().Trim());
                        string deptcode = String2Json(row["deptcode"].ToString().Trim());
                        string shopcode = String2Json(row["shopcode"].ToString().Trim());
                        string goodsnum = String2Json(row["goodsnum"].ToString());
                        string turnoverboxnum = String2Json(row["turnoverboxnum"].ToString()); // 周转箱


                        string sql = $" insert INTO dbo.dps_pick (" +
                            $" billno," +
                            $" deptcode ," +
                            $" shopcode ," +
                            $" goodsnum ," +
                            $" turnoverboxnum ," +
                            $" dealflag ," +
                            $" downflag ," +
                            $" upflag ," +
                            $" downtime " +
                            $") " +
                            $"VALUES (" +
                            $"'{billno}'," +
                            $"'{deptcode}'," +
                            $"'{shopcode}'," +
                            $"'{goodsnum}'," +
                            $"'{turnoverboxnum}'," +
                            $" 0," +
                            $" 1," +
                            $" 0," +
                            $" GETDATE()" +
                            $") ";


                        sqlList.Add(sql);
                        //LogHelpter.AddLog(sql);

                        
                        string sqlL_update = $" update wms.dps_pick set downtime=GETDATE(),downflag = 1  where billno = '{billno}' and downflag = 8 ";

                        sqlList_update.Add(sqlL_update);
                        

                        // 根据pick 获取 dtl明细数据


                        dbmanagerSql.ExecuteScalarSql(strCnn_from, "update  wms.dps_pickdtl set downflag=8 where  billno = '" + billno + "' and downflag = 0 ");
                        
                        sql_from = $" select a.*  from wms.dps_pickdtl a,wms.dps_pick b where a.billno = b.billno  and  a.downflag = 8 and a.billno = '" + billno + "'  ";   // 同步每日数据
                        int dataCount2 = 0;
                        DataTable DT = OpDB.GetDataTable(strCnn_from, sql_from);
                        if (DT != null && DT.Rows.Count > 0)
                        {
                            dataCount2 = DT.Rows.Count;
                            dataCount_dtl += dataCount2;
                            LogHelpter.AddLog("dtl数量：" + dataCount2);
                            // 全部执行

                            foreach (DataRow row_dtl in DT.Rows)
                            {

                                string dtl_billno = String2Json(row_dtl["billno"].ToString().Trim());
                                string dtl_positioncode = String2Json(row_dtl["positioncode"].ToString().Trim());
                                string dtl_shopcode = String2Json(row_dtl["shopcode"].ToString().Trim());
                                string dtl_turnoverboxcode = String2Json(row_dtl["turnoverboxcode"].ToString()); // 周转箱号
                                string dtl_shouldamount = String2Json(row_dtl["shouldamount"].ToString());
                                string dtl_actualamount = String2Json(row_dtl["actualamount"].ToString());


                                sql = $" insert INTO dbo.dps_pickdtl (" +
                                    $" billno," +
                                    $" positioncode ," +
                                    $" shopcode ," +
                                    $" turnoverboxcode ," +  // 周转箱号
                                    $" shouldamount ," +    // 应拣货数量
                                    $" actualamount ," +    // 实际拣货数量
                                    $" dealflag ," +
                                    $" downflag ," +
                                    $" upflag ," +
                                    $" downtime " +
                                    $") " +
                                    $"VALUES (" +
                                    $"'{dtl_billno}'," +
                                    $"'{dtl_positioncode}'," +
                                    $"'{dtl_shopcode}'," +
                                    $"'{dtl_turnoverboxcode}'," +
                                    $"'{dtl_shouldamount}'," +
                                    $"'{dtl_actualamount}'," +
                                    $" 0," +
                                    $" 1," +
                                    $" 0," +
                                    $" GETDATE()" +
                                    $") ";

                                sqlList.Add(sql);
                                //LogHelpter.AddLog(sql);

                                sqlL_update = $" update wms.dps_pickdtl set downtime=GETDATE(),downflag=1 where billno = '{dtl_billno}' and positioncode = '{dtl_positioncode}'  and downflag=8 ";
                                sqlList_update.Add(sqlL_update);
                                //LogBLL.Err(sqlL_update);

                            }


                        }

                        // 下发到dps
                        bool isok = OpDB.SaveDataToDB(strCnn_to, sqlList);
                        if (isok)
                        {
                            OpDB.SaveDataToDB(strCnn_from, sqlList_update); // 更新wms状态
                        }
                        else
                        {
                            LogHelpter.AddLog("下发失败");
                        }


                    }

                }


                /*if (DT_pick != null && DT_pick.Rows.Count > 0)
                {
                    // 下发到dps
                    bool isok = OpDB.SaveDataToDB(strCnn_to, sqlList);
                    if (isok)
                    {
                        OpDB.SaveDataToDB(strCnn_from, sqlList_update); // 更新wms状态
                    }
                    else
                    {
                        LogHelpter.AddLog("下发失败");
                    }
                }*/

                LogHelpter.AddLog("WMS ==》DPS [dps_pick]    下载成功，数量：" + dataCount);
                LogHelpter.AddLog("WMS ==》DPS [dps_pickdtl]    下载成功，数量：" + dataCount_dtl);
                //LogHelpter.AddLog("WMS ==》DPS [dps_pickdtl] 下载成功，数量：" + dataCount2);
            }
            catch (Exception ex)
            {
                sync_message = "同步数据下发失败，错误消息=" + ex.Message;
                sync_result = "error";
                LogHelpter.AddLog(sync_message);
            }
        }


        private static bool batchSql_dps_pick(List<DataRow> currentBatch)
        {
            LogHelpter.AddLog("行数：" + currentBatch.Count);

            string strCnn_from = AppConfigManager.LoadConfig().dbConn_wms;
            string strCnn_to = AppConfigManager.LoadConfig().dbConn_dps;

            List<string> sqlList = new List<string>();
            List<string> sqlList_update = new List<string>();


            foreach (DataRow row in currentBatch)
            {

                string billno = String2Json(row["billno"].ToString().Trim());
                string positioncode = String2Json(row["positioncode"].ToString().Trim());
                string shopcode = String2Json(row["shopcode"].ToString().Trim());
                string turnoverboxcode = String2Json(row["turnoverboxcode"].ToString()); // 周转箱号
                string shouldamount = String2Json(row["shouldamount"].ToString());
                string actualamount = String2Json(row["actualamount"].ToString());


                string sql = $" insert INTO dbo.dps_pickdtl (" +
                    $" billno," +
                    $" positioncode ," +
                    $" shopcode ," +
                    $" turnoverboxcode ," +  // 周转箱号
                    $" shouldamount ," +    // 应拣货数量
                    $" actualamount ," +    // 实际拣货数量
                    $" dealflag ," +
                    $" downflag ," +
                    $" upflag ," +
                    $" downtime " +
                    $") " +
                    $"VALUES (" +
                    $"'{billno}'," +
                    $"'{positioncode}'," +
                    $"'{shopcode}'," +
                    $"'{turnoverboxcode}'," +
                    $"'{shouldamount}'," +
                    $"'{actualamount}'," +
                    $" 0," +
                    $" 1," +
                    $" 0," +
                    $" GETDATE()" +
                    $") ";


                sqlList.Add(sql);


                string sqlL_update = $" update wms.dps_pickdtl set downtime=GETDATE(),downflag=1 where billno = '{billno}' and positioncode = '{positioncode}'  and downflag=0 ";

                sqlList_update.Add(sqlL_update);

            }

            // 下发到dps
            bool isok = OpDB.SaveDataToDB(strCnn_to, sqlList);
            if (isok)
            {
                OpDB.SaveDataToDB(strCnn_from, sqlList_update); // 更新wms状态
            }
            else
            {
                LogHelpter.AddLog("[dps_pickdtl] 下发失败,数量：" + currentBatch.Count);
            }
            return isok;
        }


        /// <summary>
        /// 过滤特殊字符
        /// </summary>
        /// <param name="s">字符串</param>
        /// <returns>json字符串</returns>
        private static string String2Json(String s)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < s.Length; i++)
            {
                char c = s.ToCharArray()[i];
                switch (c)
                {
                    case '\'':
                        sb.Append("\\\'"); break;
                    case '\"':
                        sb.Append("\\\""); break;
                    case '\\':
                        sb.Append("\\\\"); break;
                    case '/':
                        sb.Append("\\/"); break;
                    case '\b':
                        sb.Append("\\b"); break;
                    case '\f':
                        sb.Append("\\f"); break;
                    case '\n':
                        sb.Append("\\n"); break;
                    case '\r':
                        sb.Append("\\r"); break;
                    case '\t':
                        sb.Append("\\t"); break;
                    default:
                        sb.Append(c); break;
                }
            }
            return sb.ToString();
        }

    }
}
